﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;
    public class Activity
    {
        public int Add(Chain.Model.Activity model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into Activity("); 
            builder.Append("Name,StartTime,EndTime,Note,Status,flag)");
            builder.Append(" values (");
            builder.Append("@Name,@StartTime,@EndTime,@Note,@Status,@flag)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@Name", SqlDbType.NVarChar, 0x3e8),
                new SqlParameter("@StartTime", SqlDbType.DateTime),
                new SqlParameter("@EndTime",SqlDbType.DateTime),
                new SqlParameter("@Note", SqlDbType.NVarChar, 0x3e8),
                new SqlParameter("@Status",SqlDbType.Int, 4),
                new SqlParameter("@flag", SqlDbType.Int, 4)
             };
            cmdParms[0].Value = model.Name;
            cmdParms[1].Value = model.StartTime;
            cmdParms[2].Value = model.EndTime;
            cmdParms[3].Value = model.Note;
            cmdParms[4].Value = model.Status;
            cmdParms[5].Value = model.flag;

            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int ID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from Activity ");
            builder.Append(" where ID=@ID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }


        public bool Exists(int ID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from Activity");
            builder.Append(" where ID=@ID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }
        public bool Update(Chain.Model.Activity model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Activity set ");
            builder.Append("Status=@Status");
            builder.Append(" where ID=@ID");
            SqlParameter[] cmdParms = new SqlParameter[] {
                new SqlParameter("@Status",SqlDbType.Int, 4),
                 new SqlParameter("@ID", SqlDbType.Int, 4)
             };

            cmdParms[0].Value = model.Status;
            cmdParms[1].Value = model.ID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);

        }



        public DataSet GetActivityInfo(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from ");
            builder.Append("( ");
            builder.Append("select ROW_NUMBER() over(order by a.ID desc)as row,(select COUNT(*) from Mem_Activity where ActivityID=a.ID) as people,Name,StartTime,EndTime,flag,a.Status,a.ID,createdate from Activity a left join Mem_Activity b on a.ID = b.ActivityID group by Name, StartTime, EndTime, flag, a.ID,a.Status,createdate");
            builder.Append(") as T ");

            DataSet ds= DbHelperSQL.Query(builder.ToString());
            builder.AppendFormat("where T.row>={0} and T.row<{1}", (PageSize * (PageIndex - 1)) + 1, (PageSize * PageIndex) + 1);
            DataSet set = DbHelperSQL.Query(builder.ToString());
          
            resCount = ds.Tables[0].Rows.Count;
            return set;
        }

        public DataSet GetAwardsList()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ID,CONVERT(varchar(10),Nums)+'积分' as value from ScoreMoneyAwards where state=1 and isdel=0 and Status=1");
            builder.Append("select ID,CONVERT(varchar(10),Nums)+'储值' as value from ScoreMoneyAwards where state=2 and isdel=0 and Status=1");
            builder.Append("select ID,CONVERT(varchar(10),Level1)+'%,'+CONVERT(varchar(10),Level2)+'%,'+CONVERT(varchar(10),Level3)+'%' as value from RebateAwards where Status=1 and isdel=0");
            //builder.Append("select ID, CouponTitle as value from Coupon where CouponPredictNu>CouponYF ");
            builder.Append("select ID, PresentsName as value from PresentAwards where Status = 1 and isdel=0");
            builder.Append("select ID,CONVERT(varchar(10),Score)+'积分,'+CONVERT(varchar(10),Money)+'储值,'+CONVERT(varchar(10),WxPocket)+'红包' as value from Share_ExtendAwards where State=2 and isdel=0");
            builder.Append("select ID,CONVERT(varchar(10),Score)+'积分,'+CONVERT(varchar(10),Money)+'储值,'+CONVERT(varchar(10),WxPocket)+'红包' as value from Share_ExtendAwards where State=1 and isdel=0");

            DataSet set = DbHelperSQL.Query(builder.ToString());
            return set;

        }


        public bool IsCovered(int ActivityID) {

            StringBuilder builder = new StringBuilder();
            builder.Append("select flag from Activity");            
            builder.Append(" where ID=@ID");
            SqlParameter[] cmdParms = new SqlParameter[] {              
                 new SqlParameter("@ID", SqlDbType.Int, 4)
             };

            cmdParms[0].Value = ActivityID;

            DataTable dt = DbHelperSQL.Query(builder.ToString(), cmdParms).Tables[0];

            int flag = Convert.ToInt32(dt.Rows[0]["flag"].ToString());


            return (flag==1?true:false);
        }


        public DataTable ActivityShow()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ID,Name from activity where Status=1 and EndTime > GETDATE() and StartTime<GETDATE()");
           
            DataTable dt = DbHelperSQL.Query(builder.ToString()).Tables[0];
            
            return dt;
        }

    }
}
